﻿USE [sms]
GO
if exists(select name from sysobjects where name ='cp_reminder_send' and type = 'P') 
drop procedure cp_reminder_send
go  
create procedure cp_reminder_send
@outs int output--输出参数,返回结果
as
--declare @start_time datetime,
--                @end_time datetime,
--                @CONT varchar(max)     --这里使用nvarchar(max)，varchar不能包括非ASCII字符;;done
                   --@CONT 整理好的短息内容
    
begin try          
    begin
       declare  mycursor cursor for
           select t.[entryid],t.[owner],t.[start],t.[end] ,t.[content],p.[CIL],p.[Mobile] from [Reminder] t
             inner join [User] p on t.[owner]=p.[UserID]
           where t.[remindertime] > '2000-01-01 00:00:00.000' and t.[remindertime] < getdate()
           and t.[send] = 0
              
       open mycursor 
       declare @entryid varchar(150),
			  @owner int,
			  @time datetime,
              @start_time datetime,
              @end_time datetime,
              @CONT nvarchar(max),
              @from nvarchar(50),--输入参数，发件人(可以是Alias,ID,Email)
              @to nvarchar(max)---输入参数，收件人
       fetch next from mycursor
       into @entryid,@owner,@start_time,@end_time,@CONT,@from,@to
           
       while @@FETCH_STATUS=0  
           begin
              if(@start_time > '2000-01-01 00:00:00.000' and @end_time >'2000-01-01 00:00:00.000')
                 set @CONT=@CONT+' ['+CAST( @start_time AS varchar(20))+' - '+CAST( @end_time AS varchar(20))+']'
              set @to = @to + ';'
              print @from
              print @to
              print @CONT
              set @outs = 1
              execute cp_send_sms @from,@CONT,@to,@outs output
              -- print @outs
              if(@outs = 0)
              begin--如果调用成功，把已发送的reminder.send置为
                  update Reminder set send=1 where entryid=@entryid and owner=@owner
              end
              fetch next from mycursor
               into @entryid,@owner,@start_time,@end_time,@CONT,@from,@to
           end    
            
       close mycursor    
       deallocate mycursor    
    end
end try
begin catch
    set @outs=1
end catch


